package com.demo.my.Dao;

import com.demo.my.Entity.Leave;
import com.demo.my.Entity.LeaveInfoDTO;
import com.demo.my.Util.BaseDAO;
import com.demo.my.Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class LeaveDao extends BaseDAO {

    public List<LeaveInfoDTO> getLeaveList() throws Exception {
        String sql = "select" +
                " l.id,"+
                "e.employee_number,"+
                "e.name," +
                "l.startTime," +
                "l.endTime," +
                "l.days," +
                "l.type," +
                "l.reason," +
                "l.status " +
                "from lea l " +
                "inner join employee e on e.employee_number=l.employeeNumber;";
        return getList(sql, new LeaveInfoDTO(), new Object[0]);
    }
    //高和召
    public void addLeave(Leave leave) throws SQLException {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = connection.prepareStatement("insert into lea values (null,?,?,?,?,?,?,?,?,?,?)");
        ps.setObject(1,leave.getEmployeeNumber());
        ps.setObject(2,leave.getDepartmentNumber());
        ps.setObject(3,leave.getStartTime());
        ps.setObject(4,leave.getEndTime());
        ps.setObject(5,leave.getDays());
        ps.setObject(6,leave.getReason());
        ps.setObject(7,leave.getType());
        ps.setObject(8,leave.getManager());
        ps.setObject(9,leave.getStatus());
        ps.setObject(10,leave.getNotes());
        ps.executeUpdate();
        DBUtil.close(null,ps,connection);
    }

    public List<LeaveInfoDTO> getLeaveList(Integer employeeNumber) throws Exception {
        String sql = "select" +
                " l.id,"+
                "e.employee_number,"+
                "e.name," +
                "l.startTime," +
                "l.endTime," +
                "l.days," +
                "l.type," +
                "l.reason," +
                "l.status " +
                "from lea l " +
                "inner join employee e on e.employee_number=l.employeeNumber where employeeNumber=?;";
        return getList(sql, new LeaveInfoDTO(), employeeNumber);
    }

    public List<LeaveInfoDTO> getLeaveNoList() throws Exception {
        String sql = "select" +
                " l.id,"+
                "e.employee_number,"+
                "e.name," +
                "l.startTime," +
                "l.endTime," +
                "l.days," +
                "l.type," +
                "l.reason," +
                "l.status " +
                "from lea l " +
                "inner join employee e on e.employee_number=l.employeeNumber where status=?;";
        String status="未批准";
        return getList(sql, new LeaveInfoDTO(),status);
    }

    public List<LeaveInfoDTO> getLeaveListById(String id) throws Exception {
        String sql = "select" +
                " l.id,"+
                "e.employee_number,"+
                "e.name," +
                "l.startTime," +
                "l.endTime," +
                "l.days," +
                "l.type," +
                "l.reason," +
                "l.status " +
                "from lea l " +
                "inner join employee e on e.employee_number=l.employeeNumber where l.id=?;";
        return getList(sql,new LeaveInfoDTO(),id);
    }

    public void changeStatus(String id) throws SQLException {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = connection.prepareStatement("update lea set status='已批准' where id=?;");
        ps.setObject(1,id);
        ps.executeUpdate();
        DBUtil.close(null,ps,connection);
    }
}
